package dataaccess;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import domain.Car;
import domain.CarList;

public class CarDB {

	Connection con;
	Statement st;
	PreparedStatement ps;
	private InitialContext envCtx;
	private DataSource ds;

	public CarDB() {

		try {

			envCtx = new InitialContext();
			Context initContext = (Context) envCtx.lookup("java:comp/env");
			// Look up our data source
			ds = (DataSource) initContext.lookup("jdbc/mikes");
			// Obtain our environment naming context

		} catch (NamingException e) {
			e.printStackTrace();
		}
		try {
			con = ds.getConnection();
			System.out.println("connected to database");
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("can't connect to the database");
		}
		try {
			st = con.createStatement();
		} catch (SQLException e) {

			e.printStackTrace();
		}
	}

	public CarList getCarList() {
		try {
			if (con.isClosed() || con == null) {
				con = ds.getConnection();
			}
			st = con.createStatement();
			ResultSet rs;
			CarList carlist = new CarList();
			rs = st.executeQuery("Select * from car");
			while (rs.next()) {
				Car c = new Car(rs.getInt("serialNumber"),
						rs.getString("manufacturer"), rs.getString("carMake"),
						rs.getString("carModel"), rs.getString("type"),
						rs.getInt("year"), rs.getInt("scale"),
						rs.getInt("price"), rs.getString("creator"));
				carlist.insertCar(c);

			}
			return carlist;

		} catch (SQLException e) {

			e.printStackTrace();
		} finally {

			try {
				st.close();
				con.close();
				System.out.println("Connection closed");

			} catch (SQLException e) {

				e.printStackTrace();
			}

		}
		return null;
	}

	public ResultSet getCarByID(int id) {

		try {
			ResultSet rs = st.executeQuery(" select * from car where carID="
					+ id);
			return rs;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public void insertCar(Car car) {

		String query = "insert into car (serialNumber,manufacturer,carMake,carModel,type,year,scale,price,creator)"
				+ "values("
				+ car.getSerialNumber()
				+ ",'"
				+ car.getModelManufacturer()
				+ "','"
				+ car.getCarmake()
				+ "','"
				+ car.getCarModel()
				+ "','"
				+ car.getType()
				+ "',"
				+ car.getYear()
				+ ","
				+ car.getScale()
				+ ","
				+ car.getPrice()
				+ ",'" + car.getCreatorInitials() + "')";
		try {
			st.executeUpdate(query);
		} catch (SQLException e) {

			e.printStackTrace();
		}

	}
}
